<?php
	/**
	 * Created by PhpStorm.
	 * User: Administrator
	 * Date: 2021/1/11 0011
	 * Time: 8:59
	 */

	namespace App\Services;

	use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
	use PhpOffice\PhpSpreadsheet\Exception;
	use PhpOffice\PhpSpreadsheet\IOFactory;
	use PhpOffice\PhpSpreadsheet\Spreadsheet;
	use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
	use Vtiful\Kernel\Excel;

	/**
	 * 读取Excel图片并保存其路径
	 * Class ExcelImagePathServer
	 * @package App\Services
	 */
	class ExcelImagePathServer
	{
		/**
		 * @var string
		 */
		protected $relative_path = '/images';

		/**
		 * @var Spreadsheet
		 */
		protected $spreadsheet;

		/**
		 * @var Excel
		 */
		protected $xls_writer;

		/**
		 * @var Excel
		 */
		protected $sheet_writer;

		/**
		 * @var string
		 */
		protected $image_path;

		/**
		 * ExcelImagePathServer constructor.
		 * @param string $excel_file
		 * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
		 */
		public function __construct($excel_file)
		{
			$reader = IOFactory::createReader('Xlsx');
			$this->spreadsheet = $reader->load($excel_file);

			$config = ['path' => dirname($excel_file)];
			$this->xls_writer = new Excel($config);

			$this->image_path = dirname($excel_file) . $this->relative_path;
			if (!is_dir($this->image_path)) {
				mkdir($this->image_path, 0755);
			}
		}

		/**
		 * @throws Exception
		 */
		public function handle()
		{
			$write_filename = date('YmdHis') . '.xlsx';
			$sheetCount = $this->spreadsheet->getSheetCount();
			for ($i = 0; $i < $sheetCount; $i++) {
				$worksheet = $this->spreadsheet->getSheet($i);
				$data = $worksheet->toArray();
				$sheetNames = $this->spreadsheet->getSheetNames();
				var_dump($sheetCount, $sheetNames);
				// 读取并修改
				foreach ($worksheet->getDrawingCollection() as $drawing) {
					/**@var $drawing Drawing* */
					list($startColumn, $startRow) = Coordinate::coordinateFromString($drawing->getCoordinates());
					$image_filename = "/{$i}-" . $drawing->getCoordinates();
					$image_suffix = $this->saveImage($drawing, $image_filename);
					$image_name = ltrim($this->relative_path, '/') . "{$image_filename}.{$image_suffix}";
					var_dump($image_name);
					$startColumn = $this->ABC2decimal($startColumn);

					$data[$startRow - 1][$startColumn] = $image_name;
				}

				// 写入文件
				if ($i == 0) {
					$this->sheet_writer = $this->xls_writer->fileName($write_filename, $sheetNames[$i])->data($data);
				} else {
					// 向文件中追加工作表
					$this->sheet_writer->addSheet($sheetNames[$i])->data($data);
				}
			}
			// 最后的最后，输出文件
			$filePath = $this->sheet_writer->output();
			var_dump($filePath);
		}

		/**
		 * 保存图片
		 *
		 * @param Drawing $drawing
		 * @param $image_filename
		 * @return string
		 * @throws Exception
		 */
		protected function saveImage(Drawing $drawing, $image_filename)
		{
			$image_filename .= '.' . $drawing->getExtension();
			switch ($drawing->getExtension()) {
				case 'jpg':
				case 'jpeg':
					$source = imagecreatefromjpeg($drawing->getPath());
					imagejpeg($source, $this->image_path . $image_filename);
					break;
				case 'gif':
					$source = imagecreatefromgif($drawing->getPath());
					imagegif($source, $this->image_path . $image_filename);
					break;
				case 'png':
					$source = imagecreatefrompng($drawing->getPath());
					imagepng($source, $this->image_path . $image_filename);
					break;
				default:
					throw new Exception('image format error!');
			}

			return $drawing->getExtension();
		}

		/**
		 * 坐标转换
		 *
		 * @param $abc
		 * @return float|int
		 */
		protected function ABC2decimal($abc)
		{
			$ten = 0;
			$len = strlen($abc);
			for ($i = 1; $i <= $len; $i++) {
				$char = substr($abc, 0 - $i, 1);//反向获取单个字符

				$int = ord($char);
				$ten += ($int - 65) * pow(26, $i - 1);
			}
			return $ten;
		}
	}